const express = require('express');
const router = express.Router();
const http=require('http');
const multer  = require('multer');

const DBHelper = require('../utils/DBHelper');
const sql = require('../sqlMap');
var url = require('url');
var fs = require("fs");
const path = require('path') 
var urlencode = require('urlencode');

let pool = new DBHelper().getpool();
let connection = new DBHelper().getConnection();

const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, 'swc/') // 确保这个文件夹已经存在
    },
    filename: function (req, file, cb) {
        // console.log(req.params);

        cb(null, file.originalname )
    }
})
const upload = multer({ storage: storage });
 
// 增加用户
router.post('/addUser', (req, res) => {
    let sqlStr = sql.user.add;
    let params = req.body;
    let dbhelper = new DBHelper();
    dbhelper.query(sqlStr);
});

// 查询用户
router.post('/selectUser', (req, res) => {
    let sqlStr = sql.user.select;
    let params = req.body;
    let dbhelper = new DBHelper();
    dbhelper.query(sqlStr);
});
router.post('/login', (req, res) => {
    let sqlStr = sql.user.select;
    let params = req.body;
    console.log(req.body);
    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr,[params.username,params.password], function(err, rows) {
                if (err) {
                     console.error(err);
                    res.send({
                        status: 1, // 0成功，1失败
                        msg: "POST请求失败！"
                      });
                    return false;
                } else {
                    console.log(rows);
                    if(rows.length>0){
                        updatetimestr = "update users set lastlogintime = NOW() where username = '" + params.username + "'";
                        connection.query(updatetimestr);
                    res.send({
                        status: 0, // 0成功，1失败
                        msg: "登录成功",
                        data: rows[0]['authority'], // 响应给客户端的数据
                      });
                    }else{
                                            res.send({
                        status: 1, // 0成功，1失败
                        msg: "登录失败"
                      });
                    }
                }

            });
            connection.release();

        });
    // res.send(req.body);
});


// 查询sample
router.get('/selectSample', (req, res) => {

    var params0 = url.parse(req.url, true).query;
    // params0.sample_id=Buffer(params0.sample_id, 'base64').toString();
    let sqlStr;
    if(params0.sample_id==null){
        console.log(params0.sample_id)
        return;
    }else if(params0.sample_id=='all')
        sqlStr= sql.sample.select;
    else if(params0.sample_id=='PFC' || params0.sample_id=='HIP'|| params0.sample_id=='HY'|| params0.sample_id=='OTHER'|| params0.sample_id=='ISO')
        sqlStr= sql.sample.selectwithtype+' and type= "' +params0.sample_id+'"';
    else{
        
        console.log(params0.sample_id)
        sqlStr= sql.sample.select+' where sampleid='+params0.sample_id;

        // return;
    }
    let params = req.body;
    let dbhelper = new DBHelper();
    dbhelper.query(sqlStr,res,function(err,rows){
                    if (err) {
                     console.error(err);
                    return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }
            });

});
router.post('/uploadFile',upload.array('file'),(req,res)=>{
    let sampleid = req.body['sampleid'];
    let dir = '/home/dell/NeuronServer/data/swc/'+sampleid+'/';
    fs.access(dir,function(err){
        if(err){
            console.log(dir+'mkdir')

            fs.mkdir(dir,(err)=>{
                if(!err){
                    const files = req.files;
                    if (!files) {
                        // console.log(req)
                        return res.status(400).send('No file uploaded.');
                    }
                    // console.log(req.files)
                    let sourcename = "swc/"+req.files[0].originalname;
                    let destinationname = dir+''+req.files[0].originalname.split("-")[1]
                    console.log(sourcename,destinationname);
                    fs.rename(sourcename,destinationname,(err)=>{
                        res.send('File uploaded successfully.');

                    })
                }
                else{
                    console.log("mkdir err")
                }
            })
            return;
        }
        console.log(dir+'dir exists')
        const file = req.files[0];
        if (!file) {
            return res.status(400).send('No file uploaded.');
        }
        // console.log(req.files[0])
        let sourcename = "swc/"+req.files[0].originalname;
        let destinationname = dir+'/'+req.files[0].originalname.split("-")[1]
        console.log(sourcename,destinationname);
        fs.rename(sourcename,destinationname,(err)=>{
            res.send('File uploaded successfully.');

        })

    })

})


router.post('/updateNeuron',(req,res)=>{
 
    sqlStr = sql.neuron.update;
    console.log("addSample: "+ sqlStr)
    let params = req.body;
    let path = "./data/swc/"+params.sampleid+"/"+params.name;
    if (params.properties==null)
        params.properties=""
    pool.getConnection(function(err, connection) {
        connection.query(sqlStr,[params.name,params.sampleid,path,params.properties,params.region,params.position,params.hemisphere,params.type,params.exclude,params.comment], function(err, rows) {
            if (err) {
                console.error(err);
                return false;
            } else {
                console.log(rows);
                res.json(rows);
            }
            });
            connection.release();

        });
})

router.post('/addSample',(req,res)=>{
    addSampleStr = sql.sample.add;
    console.log("addSample: "+ addSampleStr)
    let params = req.body;
    pool.getConnection(function(err, connection) {
        connection.query(addSampleStr,[params.sampleid,params.pi,params.region,params.neuroncount,params.species], function(err, rows) {
            if (err) {
                console.error(err);
                return false;
            } else {
                console.log(rows);
                res.json(rows);
            }
            });
        // update rolesample
        updaterolesampleStr=sql.sample.updateRolesample;
                
        connection.query(updaterolesampleStr,[params.sampleid],function(err,rows){
            if (err) {
                console.error(err);
                return false;
            } else {

            }
        });
            connection.release();

        });

    console.log("addSample")
})

// 查询sample by user role
router.post('/getSample',(req,res)=>{
    let rolesqlStr = sql.user.role;
    let params = req.body;

    //get roles
    // let pool = new DBHelper().getpool();
    pool.getConnection(function(err, connection) {
        connection.query(rolesqlStr,[params.username], function(err, roles) {
            if (err) {
                console.error(err);
                return false;
            } else {
                console.log(roles);
                var normal=false
                var all=false
                var proj=false
                var projs=[]
                for (i =0;i<roles.length;i++){
                    if (roles[i]['rightname']=='all'){
                        all=true;
                    }
                    else if (roles[i]['rightname']=='normal'){
                        normal=true;
                    }else{
                        proj=true;
                        projs.push(roles[i]['rightname']);
                    }
                }
                console.log(all,normal,proj)
                if (all){
                        sampleSql='select * from samples';
                        if (params.length==1){
                            sampleSql+=' order by sampleid;';
                        }else{
                            sampleSql+=' where '
                            for(var key in params){
                                if (key !='username'){
                                   sampleSql += key + " LIKE '%"+ params[key]+"%' and "
                                }
                            }
                            sampleSql=sampleSql.substr(0,sampleSql.length-4)+ " ORDER BY sampleid;";

                        }
                        console.log(sampleSql)
                        connection.query(sampleSql,function(err,samples){
                            console.log(samples.length)
                            res.send({
                                    status: 0, // 0成功，1失败
                                    msg: "POST请求成功！",
                                    data:samples
                                    });
                        })
                }

                else if (normal ||proj){
                    // get group
                    connection.query(sql.user.group,[params.username], function(err, group) {
                    if (err) {
                        console.error(err);
                        return false;
                    } else {
                        console.log(group);
                        if (!proj){
                            sampleSql='select * from samples';
                            if (params.length==1){
                                sampleSql+=' order by sampleid;';
                            }else{
                                sampleSql+=' where '
                                for(var key in params){
                                    if (key !='username'){
                                       sampleSql += key + " LIKE '%"+ params[key]+"%' and "
                                    }
                                }
                                sampleSql +=" pi = '"+group[0]['groupname']+"'";
                                sampleSql=sampleSql+ " ORDER BY sampleid;";

                            }
                            console.log(sampleSql)
                            connection.query(sampleSql,function(err,samples){
                                console.log(samples)
                                res.send({
                                        status: 0, // 0成功，1失败
                                        msg: "POST请求成功！",
                                        data:samples
                                        });
                            })
                        }
                        else{
                            var sqlstring = "select * from samples inner join rolesample on samples.sampleid=rolesample.sample and (rolesample.role ='" + projs[0] + "'";// or samples.pi= '" + group + "') and ";
                            for (i = 1; i < projs.length; i++) {
                                sqlstring += " or rolesample.role ='" + projs[i]+"'";
                            }
                            sqlstring += " or samples.pi = '" + group[0]['groupname'] + "') and ";
                            for(var key in params){
                                    if (key !='username'){
                                       sqlstring += key + " LIKE '%"+ params[key]+"%' and "
                                    }
                            }
                            sqlstring = sqlstring.substr(0, sqlstring.length - 4) + " ORDER BY sampleid;";
                            console.log(sqlstring)
                            connection.query(sqlstring,function(err,samples){
                                console.log(samples.length)
                                res.send({
                                        status: 0, // 0成功，1失败
                                        msg: "POST请求成功！",
                                        data:samples
                                        });
                            })
                        }

                    }

                    });
                }

            }
        });
        connection.release();

    });


});

// 查询neuron only by sampleid
router.get('/selectNeurons', (req, res) => {
    let sqlStr = sql.neuron.select;
    // let params = req.body;
    var params = url.parse(req.url, true).query;

    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr,[params.id], function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }

            });
            connection.release();

        });
});

// 查询neuron 
router.post('/getNeurons', (req, res) => {
    let sqlStr = "select name,sampleid,region from neurons where ";
    let params = req.body;
    // var params = url.parse(req.url, true).query;
    for(var key in params){
       sqlStr += key + " = '"+ params[key]+"' and "
    }
    sqlStr = sqlStr.substr(0, sqlStr.length - 4)
    console.log(sqlStr)
	// let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr,[params.sampleid], function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }

            });
            connection.release();

        });
});

//查询 PI
router.get('/getPI',(req,res)=>{


    let sqlStr=sql.user.pi;
    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr, function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }

            });
            connection.release();

        });
});

//查询 regions
router.get('/getRegion',(req,res)=>{
    let sqlStr=sql.user.region;
    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr, function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }

            });
            connection.release();

        });
});

// projectregion
router.get('/projectRegion', (req, res) => {
    let sqlStr = sql.neuron.projectregion;
    // let params = req.body;
    var params = url.parse(req.url, true).query;
    if(params.region==null)
        params.region='';

    console.log(params)
    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr,["%\""+params.region+"\"%"], function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }
            });
            connection.release();
        });
});

// terminalregion
router.get('/terminalRegion', (req, res) => {
    let sqlStr = sql.neuron.terminalregion;
    // let params = req.body;
    var params = url.parse(req.url, true).query;
    if(params.region==null)
        params.region='';

    console.log(params)
    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr,["%"+params.region+"%"], function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }
            });
            connection.release();
        });
});



// neuronsBySoma
router.get('/neuronsBySoma', (req, res) => {
    let sqlStr = sql.neuron.somaregion;
    // let params = req.body;
    var params = url.parse(req.url, true).query;
    if(params.region==null)
        params.region='';

    console.log(params)
    // let pool = new DBHelper().getpool();
        pool.getConnection(function(err, connection) {
            connection.query(sqlStr,["%"+params.region+"%"], function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }

            });
            connection.release();

        });
});
function query(sql,sampleid,neuronid) {
    return new Promise((resolve, reject) => {
        // 从连接池获取连接对象
        // const conn = pool.getConnection();
        
        // 执行SQL语句并处理结果
        connection.query(sql,[sampleid,neuronid], (err, results) => {
            if (err) {
                reject(err);
            } else {
                resolve(results);
            }
            
            // 释放连接
            // connection.release();
        });
    });
}

async function published(req,sampleid,neuronid) {
    let sqlStr = sql.neuron.public;

    var bPublished=0;
    const result = await query(sqlStr,sampleid,neuronid);
    // console.log(result[0]['public']);
    if(result[0]['public']=='1'){
        bPublished=1;
    }
    return bPublished;
}
router.get('/public',(req,res)=>{
    var params = url.parse(req.url, true).query;
    if(params.sampleid==null)
        params.sampleid='';
    if(params.neuronid==null)
        params.neuronid='';
    var result = published(req,params.sampleid,params.neuronid).then(res0=>{
        // console.log('wxf',res0);
        res.json(res0);
    })

});

// 查询neuron property
router.get('/neuronProperty', (req, res) => {
    const hostname = req.headers.host;
    console.log(hostname);
    if(hostname!='10.10.48.110:8888'){
        console.log(hostname,'not allowed');
        res.end()
        return; 
    }

    let sqlStr = sql.neuron.property;
    // let params = req.body;
    var params = url.parse(req.url, true).query;
    if(params.sampleid==null)
        params.sampleid='';
    if(params.neuronid==null)
        params.neuronid='';
    console.log(params,"neuronProperty")
        pool.getConnection(function(err, connection) {
            if(!err){
                connection.query(sqlStr,[params.sampleid,params.neuronid], function(err, rows) {
                    if (err) {
                         console.error(err);
                              return false; 
                    } else {
                        if(rows.length){
                            // console.log(rows[0]['properties']);
                            res.json(rows[0]['properties']);
                        }
                    }

                });
                connection.release();
            }else{
                res.statusCode=502
                res.send({
                            status: 502, // 0成功，1失败
                            msg: "POST请求失败！"
                            });
            }

        });
});


router.get('/getSampleInfo',(req,res)=>{
    let sqlStr = sql.sampleinfo.select;
        var params = url.parse(req.url, true).query;
        if(params.project_id==null)
            params.project_id=''
        if(params.fMOST_id==null)
            params.fMOST_id=''
        console.log(params)
        // let pool = new DBHelper().getpool();
        let regionMap = new Map();
        regionMap.set(0, "zero");
        var global_data = fs.readFileSync("annot.txt").toString();
        var array = global_data.toString().split("\n");
            for(var j=0;j<array.length;j++){
                var regionarr = array[j].toString().split(":");
                regionMap.set(regionarr[2], regionarr[0]);
            }
        // fs.readFile("annot.txt", 'utf-8', (err, data) => {
        //     if (err) throw err;
        //     var array = data.toString().split("\n");
        //     for(var j=0;j<array.length;j++){
        //         var regionarr = array[j].toString().split(":");
        //         regionMap.set(regionarr[2], regionarr[0]);
        //     }
        //      // console.log(regionMap)
        // });
        console.log(regionMap.get('ACA'))
        pool.getConnection(function(err, connection) {
            
            connection.query(sqlStr,[params.project_id,params.fMOST_id], function(err, rows) {
                // console.log(regionMap);
                if (err) {
                    throw err;
                } else {
                    // console.log(rows);
                    for (var i = 0;i<rows.length;i++){
                        injection_region=rows[i].injection_region;
                        var arr = injection_region.toString().split(";");
                        rows[i].injection_region_id=''
                        for(var j=0;j<arr.length;j++){
                           rows[i].injection_region_id=rows[i].injection_region_id+regionMap.get(arr[j])+';'
                        }

                        injection_structure=rows[i].injection_structure;
                        var arr = injection_structure.toString().split(";");
                        rows[i].injection_structure_id=''
                        for(var j=0;j<arr.length;j++){
                            if(arr[j]!='')
                           rows[i].injection_structure_id=rows[i].injection_structure_id+regionMap.get(arr[j])+';'
                        }
                        console.log(rows[i]);
                    }
                    res.json(rows);
                }

            });
            connection.release();

        });
});


router.get('/getSWC', (req, res) => {
    const hostname = req.headers.host;
    console.log(hostname);
    var allow=true;
    if(hostname!='10.10.48.110:8888'){
        console.log(hostname,'not allowed');

        allow=false;
    }
    var root = "";
    var params = url.parse(req.url, true).query;
    if(params.sampleid==null)
        params.sampleid='';
    if(params.neuronid==null)
        params.neuronid='';
    console.log(params)

    const os = require('os');
    if (os.type() == 'Windows_NT') {
        //windows
        root="D:/project/FNTManager/build/install/data/swc";
    } else if (os.type() == 'Darwin') {
           //mac
    } else if (os.type() == 'Linux') {
        //Linux
        root="/home/dell/NeuronServer/data/swc";
    } else {
                //不支持提示
    }

            // let params = req.body;
    var fs = require("fs");
    var filename = root + "/" + params.sampleid + "/" + params.neuronid;
    // console.log(filename);
    var result = published(req,params.sampleid,params.neuronid).then(res0=>{
        // console.log('wxf',res0);
        if(res0==1){

            fs.readFile(filename,(err,data)=>{
                if(err){
                    console.log('error',err);
                }
                else{
                    // console.log(data.toString());
                    res.write(data.toString());
                    res.end()
                }
            })
        }else if(res0==0 && allow!=false){
                fs.readFile(filename,(err,data)=>{
                if(err){
                    console.log('error',err);
                }
                else{
                    // console.log(data.toString());
                    res.write(data.toString());
                    res.end()
                }
            })

             console.log('allowed',res0,allow);

        }else{
            res.end();

           console.log('not allowed ',res0,allow);

        }
    })


 
});
router.post('/getSWC', (req, res) => {

    var root = "";
    let params = req.body;
    if(params.sampleid==null)
        params.sampleid='';
    if(params.neuronid==null)
        params.neuronid='';
    console.log(params)
    const os = require('os');
    if (os.type() == 'Windows_NT') {
        //windows
        root="D:/project/FNTManager/build/install/data/swc";
    } else if (os.type() == 'Darwin') {
        //mac
    } else if (os.type() == 'Linux') {
        //Linux
        root="/home/dell/NeuronServer/data/swc";

    } else {
        //不支持提示
    }

    // let params = req.body;
    var fs = require("fs");
    var filename = root + "/" + params.sampleid + "/" + params.neuronid;
    // console.log(filename);
    fs.readFile(filename,(err,data)=>{
        if(err){
            console.log('error',err);
        }
        else{
            // console.log(data.toString());
            res.write(data.toString());
            res.end()
        }
    })
 
});
// 查询sample
router.get('/getSoma', (req, res) => {
    var params = url.parse(req.url, true).query;
    if(params.project_id==null)
        params.project_id=''
    if(params.fMOST_id==null)
        params.fMOST_id=''

    let sqlStr = sql.neuron.soma;
    
    // let pool = new DBHelper().getpool();
    pool.getConnection(function(err, connection) {
            connection.query(sqlStr,[params.fMOST_id], function(err, rows) {
                if (err) {
                     console.error(err);
                          return false; 
                } else {
                    console.log(rows);
                    res.json(rows);
                }

            });
            connection.release();

    });

    // conn.end();
});


// 查询sample
router.post('/getAllSoma', (req, res) => {
    let sqlStr = sql.neuron.allsoma;
    let params = req.body;
    let dbhelper = new DBHelper();
    dbhelper.query(sqlStr,res);
    // conn.end();
});
module.exports = router;
